package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.util.Pager;
import SiTuQingYou.vo.PackageTour;

public class PackageTourDao
{
	private  PackageTourDao() {}
	private static  PackageTourDao ptd= new  PackageTourDao();
	public static  PackageTourDao getptd()
	{
		return ptd;
	}
	public void  mopack(PackageTour pack) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		conn = DBHelper.getDB().getConn();
		try {
			String sql = "update packagetour set people=?,price=?,title=?,infomation=?,packtime=? where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pack.getPeople());
			ps.setFloat(2, pack.getPrice());
			ps.setString(3, pack.getTitle());
			ps.setString(4, pack.getInfomation());
			ps.setTimestamp(5, pack.getPacktime());
			ps.setInt(6, pack.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDB().close(rs, ps, conn);
		}
		
	}
	
	
	//查找一个导游团信息
	public PackageTour onepack(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		conn  = DBHelper.getDB().getConn();
		try {
			String sql = "select * from packagetour where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			PackageTour pack = new PackageTour();
			if(rs.next()) {
				pack.setTitle(rs.getString("title"));
				pack.setInfomation(rs.getString("infomation"));
				pack.setPeople(rs.getInt("people"));
				pack.setPirce(rs.getFloat("price"));
				pack.setPacktime(rs.getTimestamp("packtime"));
				pack.setPhoto(rs.getString("photo"));
			}
			return pack;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	//删除单个导游团
	public void delete(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {	
			conn = DBHelper.getDB().getConn();
			String sql = "update packagetour set fakedelete=1 where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
	}
	
	public void addpack(PackageTour pack) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getDB().getConn();
			String sql = "insert into packagetour(title,infomation,photo,people,price,packtime) value(?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1,pack.getTitle());
			ps.setString(2, pack.getInfomation());
			ps.setString(3, pack.getPhoto());
			ps.setInt(4, pack.getPeople());
			ps.setFloat(5, pack.getPrice());
			ps.setTimestamp(6, pack.getPacktime());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDB().close(rs, ps, conn);
		}
	}
	
	
	
	
	//查询出所有跟团游信息（发团日期和已过期的不显示）
	public ArrayList<PackageTour> lookForAllPackageTour(Pager pager) {
		pager.setDataCount(this.getPackageTourCount());
		ArrayList<PackageTour> pat = new ArrayList<PackageTour>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "select * from packagetour where  packtime>=CURDATE()+1 and fakedelete=0 order by packtime limit ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pager.getFirstRow());//获得limit后面第一个数是几
			ps.setInt(2, pager.getPageSize());//获得每页几条记录
			rs = ps.executeQuery();
			while(rs.next()) {
				PackageTour p = new PackageTour();
				p.setId(rs.getInt("id"));
				p.setPhoto(rs.getString("photo"));//旅游团图片
				p.setInfomation(rs.getString("infomation"));//旅游团详情
				p.setPirce(rs.getFloat("price"));//旅游团价格
				p.setPeople(rs.getInt("people"));//旅游团人数
				p.setPacktime(rs.getTimestamp("packtime"));//发团时间
			    p.setTitle(rs.getString("title"));//主题
			    pat.add(p);
			}
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return pat;
		
	}
	
	
	//获得所有跟团游数量
	public int getPackageTourCount() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		int count =0;
		
		try {
			conn = DBHelper.getDB().getConn();
			String sql = "select count(id) as sccount from packagetour where packtime>=CURDATE()+1 and fakedelete=0";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt("sccount");
			} 
		} 
		catch (Exception e) {
			// TODO Auto-generated catch block
		    e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return count;
		
	}
	
	//导游团加入购物车
	public void addToShoppingCart(int userid,int packageid){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "insert into packcar(userid,packagetourid)value(?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,userid);
			ps.setInt(2,packageid);
			ps.executeUpdate();
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		
		
		
	}

}
